use crate::model::date::{Date, Day, Month, Week, Year};
use chrono::{DateTime, Datelike};
use rocket_db_pools::sqlx::{self, Row, Sqlite, Transaction};

pub async fn update_stat_time<'a, T: chrono::TimeZone>(
    db: &mut Transaction<'a, Sqlite>,
    time: DateTime<T>,
    increment: u32,
) -> Result<(), sqlx::Error> {
    let year: u16 = time.year().try_into().unwrap();
    let month: u8 = time.month().try_into().unwrap();
    let day: u8 = time.day().try_into().unwrap();
    let week: u8 = time.iso_week().week().try_into().unwrap();
    update_stat(db, Date::Year(year), increment).await?;
    update_stat(db, Date::Month(Month { year, month }), increment).await?;
    update_stat(db, Date::Week(Week { year, week }), increment).await?;
    update_stat(db, Date::Day(Day { year, month, day }), increment).await?;
    Ok(())
}

async fn update_stat<'a>(
    db: &mut Transaction<'a, Sqlite>,
    date: Date,
    increment: u32,
) -> Result<(), sqlx::Error> {
    match date {
        Date::Year(year) => {
            sqlx::query(
                r#"
                    INSERT INTO total_yearly (year, count)
                    VALUES (?1, ?2)
                    ON CONFLICT(year)
                    DO UPDATE SET count = count + ?2
                "#,
            )
            .bind(year)
            .bind(increment)
            .execute(&mut **db)
            .await?;
        }
        Date::Month(month) => {
            sqlx::query(
                r#"
                    INSERT INTO total_monthly (id, year, month, count)
                    VALUES (?1, ?2, ?3, ?4)
                    ON CONFLICT(id)
                    DO UPDATE SET count = count + ?4
                "#,
            )
            .bind(month.get_id())
            .bind(month.year)
            .bind(month.month)
            .bind(increment)
            .execute(&mut **db)
            .await?;
        }
        Date::Week(week) => {
            sqlx::query(
                r#"
                    INSERT INTO total_weekly (id, year, week, count)
                    VALUES (?1, ?2, ?3, ?4)
                    ON CONFLICT(id)
                    DO UPDATE SET count = count + ?4
                "#,
            )
            .bind(week.get_id())
            .bind(week.year)
            .bind(week.week)
            .bind(increment)
            .execute(&mut **db)
            .await?;
        }
        Date::Day(day) => {
            sqlx::query(
                r#"
                    INSERT INTO total_daily (id, year, month, day, count)
                    VALUES (?1, ?2, ?3, ?4, ?5)
                    ON CONFLICT(id)
                    DO UPDATE SET count = count + ?5
                "#,
            )
            .bind(day.get_id())
            .bind(day.year)
            .bind(day.month)
            .bind(day.day)
            .bind(increment)
            .execute(&mut **db)
            .await?;
        }
    };
    Ok(())
}

pub async fn get_year_stat<'a>(
    db: &mut Transaction<'a, Sqlite>,
    start: Year,
    end: Year,
) -> Result<Vec<(Year, u32)>, sqlx::Error> {
    sqlx::query(
        r#"
            SELECT year, count FROM total_yearly
            WHERE year BETWEEN ?1 AND ?2
        "#,
    )
    .bind(start.0)
    .bind(end.0)
    .try_map(|row| Ok((Year(row.try_get(0)?), row.try_get::<u32, usize>(1)?)))
    .fetch_all(&mut **db)
    .await
}

pub async fn get_month_stat<'a>(
    db: &mut Transaction<'a, Sqlite>,
    start: Month,
    end: Month,
) -> Result<Vec<(Month, u32)>, sqlx::Error> {
    sqlx::query(
        r#"
            SELECT year, month, count FROM total_monthly
            WHERE id BETWEEN ?1 AND ?2
        "#,
    )
    .bind(start.get_id())
    .bind(end.get_id())
    .try_map(|row| {
        Ok((
            Month {
                year: row.try_get(0)?,
                month: row.try_get(1)?,
            },
            row.try_get::<u32, usize>(2)?,
        ))
    })
    .fetch_all(&mut **db)
    .await
}

pub async fn get_week_stat<'a>(
    db: &mut Transaction<'a, Sqlite>,
    start: Week,
    end: Week,
) -> Result<Vec<(Week, u32)>, sqlx::Error> {
    sqlx::query(
        r#"
            SELECT year, week, count FROM total_weekly
            WHERE id BETWEEN ?1 AND ?2
        "#,
    )
    .bind(start.get_id())
    .bind(end.get_id())
    .try_map(|row| {
        Ok((
            Week {
                year: row.try_get(0)?,
                week: row.try_get(1)?,
            },
            row.try_get::<u32, usize>(2)?,
        ))
    })
    .fetch_all(&mut **db)
    .await
}

pub async fn get_daily_stat<'a>(
    db: &mut Transaction<'a, Sqlite>,
    start: Day,
    end: Day,
) -> Result<Vec<(Day, u32)>, sqlx::Error> {
    sqlx::query(
        r#"
            SELECT year, month, day, count FROM total_daily
            WHERE id BETWEEN ?1 AND ?2
        "#,
    )
    .bind(start.get_id())
    .bind(end.get_id())
    .try_map(|row| {
        Ok((
            Day {
                year: row.try_get(0)?,
                month: row.try_get(1)?,
                day: row.try_get(2)?,
            },
            row.try_get::<u32, usize>(3)?,
        ))
    })
    .fetch_all(&mut **db)
    .await
}
